using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Workflows.Components;
using Workflows.Components.DB.Properties;

namespace Workflows.Components.DB.Sql
{
    /// <summary></summary>
    [Serializable]
    public class SqlComponent:WorkflowComponentBase
    {
        private SqlInstruction _Instruction;
        private SqlOutput _Output;

        #region props
        public override InstructionBase Instruction
        {
            get { return _Instruction; }
            set { _Instruction =(SqlInstruction) value; }
        }

        public override ResultBase Output
        {
            get { return _Output; }
            set { _Output =(SqlOutput) value; }
        }
        /// <summary></summary>
        public string ConnStr
        {
            get { return this._Instruction.ConnStr; }
            set { this._Instruction.ConnStr = value; }
        }
        /// <summary></summary>
        public string SqlCommandText
        {
            get { return this._Instruction.SqlCommandText; }
            set { this._Instruction.SqlCommandText = value; }
        }
        /// <summary></summary>
        public CommandType SqlCommandType
        {
            get { return this._Instruction.SqlCommandType; }
            set { this._Instruction.SqlCommandType = value; }
        }
        /// <summary></summary>
        public SimpleSqlParameter[] Parameters
        {
            get { return this._Instruction.Parameters; }
            set { this._Instruction.Parameters = value; }
        }
        /// <summary></summary>
        public SqlQueryOutputType QueryOutputType
        {
            get { return this._Instruction.QueryOutputType; }
            set { this._Instruction.QueryOutputType = value; }
        }
        /// <summary></summary>
        public SimpleDataType QueryScalarOutputType
        {
            get { return this._Instruction.QueryScalarOutputType; }
            set { this._Instruction.QueryScalarOutputType = value; }
        }
        /// <summary></summary>
        public DataTable QueryOutputTableDefinition
        {
            get { return this._Instruction.QueryOutputTableDefinition; }
            set { this._Instruction.QueryOutputTableDefinition = value; }
        }
        #endregion
        /// <summary></summary>
        public SqlComponent():base()
        {
            this.ComponentName = "SQL task";
            this.Description = "Execute SQL statement or stored procedure";
            this.ComponentIcon = Resources.SqlWb_10000;
            this.ComponentImage = Resources.Tool;
            this.ComponentActivityType = WorkflowActivityType.SimpleActivity;
            this.ComponentCategory = WorkflowComponentCategory.Database_Tasks.ToString();

            this._Instruction =
                new SqlInstruction(this.ComponentID, Guid.Empty, string.Empty, string.Empty, CommandType.Text, null,
                                   SqlQueryOutputType.Nothing, SimpleDataType.IntNumberType, null);
            this._Output = new SqlOutput(this.ComponentID, Guid.Empty, SqlOutputType.Nothing, null, null, null);
        }

        public override XmlNode Serialize(ref XmlDocument xDoc, ref XmlNode parentNode)
        {
            XmlNode xNode = base.Serialize(ref xDoc, ref parentNode);

            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "ConnectionString", this.ConnStr);
            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "CommandText", this.SqlCommandText);
            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "CommandType", ((int) this.SqlCommandType).ToString());
            XmlNode parameterRootNode = XmlDataUtil.AddElement(ref xDoc, xNode, "SqlParameters");
            if (this.Parameters != null && this.Parameters.Length > 0)
            {
                foreach (SimpleSqlParameter sqlParam in this.Parameters)
                {
                    XmlNode paramNode = XmlDataUtil.AddElement(ref xDoc, parameterRootNode, "SqlParameter");
                    XmlDataUtil.UpdateAttribute(ref xDoc, paramNode, "ParameterName", sqlParam.ParameterName);
                    XmlDataUtil.UpdateAttribute(ref xDoc, paramNode, "DataType", ((int) sqlParam.DbType).ToString());
                    XmlDataUtil.UpdateAttribute(ref xDoc, paramNode, "Direction", ((int) sqlParam.Direction).ToString());
                    XmlDataUtil.UpdateAttribute(ref xDoc, paramNode, "ParameterValue",
                                                sqlParam.Value is DBNull ? string.Empty : sqlParam.Value.ToString());
                }
            }

            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "QueryOutputType", ((int)this.QueryOutputType).ToString());
            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "QueryScalarOutputType", ((int)this.QueryScalarOutputType).ToString());
            if (this.QueryOutputTableDefinition != null && this.QueryOutputTableDefinition.Columns.Count > 0)
            {
                XmlNode outputTableDefNode = XmlDataUtil.AddElement(ref xDoc, xNode, "QueryTableOutputDefinition");
                foreach (DataColumn col in this.QueryOutputTableDefinition.Columns)
                {
                    XmlNode colNode = XmlDataUtil.AddElement(ref xDoc, outputTableDefNode, "Column");
                    XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "ColumnName", col.ColumnName);
                    XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "DataType", col.DataType.Name);
                }
            }

            return xNode;
        }

        public override WorkflowComponentBase Instantiate(XmlNode xNode)
        {
            if (xNode.Attributes.GetNamedItem("Type").Value == this.GetType().FullName)
            {
                SqlComponent component = base.Instantiate(xNode) as SqlComponent;

                component.ConnStr = xNode.Attributes.GetNamedItem("ConnectionString").Value;
                component.SqlCommandText = xNode.Attributes.GetNamedItem("CommandText").Value;
                component.SqlCommandType = (CommandType) int.Parse(xNode.Attributes.GetNamedItem("CommandType").Value);
                List<SimpleSqlParameter> parameters = new List<SimpleSqlParameter>();
                XmlNodeList paramNodes = xNode.SelectNodes("SqlParameters/SqlParameter");
                if (paramNodes != null && paramNodes.Count > 0)
                {
                    foreach (XmlNode paramNode in paramNodes)
                    {
                        SimpleSqlParameter sqlParam = new SimpleSqlParameter();
                        sqlParam.ParameterName = paramNode.Attributes.GetNamedItem("ParameterName").Value;
                        sqlParam.DbType = (DbType) int.Parse(paramNode.Attributes.GetNamedItem("DataType").Value);
                        sqlParam.Direction =
                            (ParameterDirection) int.Parse(paramNode.Attributes.GetNamedItem("Direction").Value);
                        string paramValue = paramNode.Attributes.GetNamedItem("ParameterValue").Value;
                        if (string.IsNullOrEmpty(paramValue))
                        {
                            sqlParam.Value = DBNull.Value;
                        }
                        else
                        {
                            sqlParam.Value = SqlDbTypeUtil.GetValueFromString(paramValue, sqlParam.DbType);
                        }
                        parameters.Add(sqlParam);
                    }
                }
                component.Parameters = null;
                if (parameters.Count > 0)
                {
                    component.Parameters = parameters.ToArray();
                }

                component.QueryOutputType =
                    (SqlQueryOutputType)int.Parse(xNode.Attributes.GetNamedItem("QueryOutputType").Value);
                component.QueryScalarOutputType =
                    (SimpleDataType) int.Parse(xNode.Attributes.GetNamedItem("QueryScalarOutputType").Value);
                component.QueryOutputTableDefinition = null;
                XmlNodeList colNodes = xNode.SelectNodes("QueryTableOutputDefinition/Column");
                if (colNodes != null && colNodes.Count > 0)
                {
                    DataTable tbl = new DataTable();
                    foreach (XmlNode colNode in colNodes)
                    {
                        string colName = colNode.Attributes.GetNamedItem("ColumnName").Value;
                        Type colDbType = Type.GetType(colNode.Attributes.GetNamedItem("DataType").Value);
                        if (colDbType == null || colDbType == Type.Missing.GetType())
                        {
                            colDbType = typeof(string);
                        }
                        tbl.Columns.Add(colName, colDbType);
                    }
                    component.QueryOutputTableDefinition = tbl;
                }

                return component;
            }
            else
            {
                throw new Exception("Unable to instantiate component: invalid type specified in xml node");
            }
        }
    }
}
